﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using WmsApp.Factory;
using WmsApp.Security;
using WmsApp.WmsService;

namespace WmsApp.Export
{
    public class ExcelImport : IExcelImport
    {
        #region IExcelImport Members

        public List<InStoreBillDtl> ReadGoodsCodeTypeFromExcel(string excelFile, string billID)
        {
            var inStoreBillDtls = new List<InStoreBillDtl>();
            DataTable excelTable = null;
            var ds = new DataSet();

            var conn =
                new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";" +
                                    "Extended Properties=Excel 8.0;");
            conn.Open();
            //var schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            var tableName = @"库存$"; //Excel 的表名
            var strSql = "select * from [" + tableName + "]";
            var myData = new OleDbDataAdapter(strSql, conn);
            myData.Fill(ds, tableName);

            conn.Close();

            excelTable = ds.Tables[tableName];

            var client = WmsServiceFactory.GetServiceClient();

            var idx = 0;
            foreach (DataRow row in excelTable.Rows)
            {
                if (String.IsNullOrEmpty(row["物料代码"].ToString().Trim()))
                {
                    continue;
                }
                var dtl = InStoreBillDtl.CreateInStoreBillDtl(billID);

                dtl.NewGoodsCode = row["物料代码"].ToString().Trim();
                dtl.NewGoodsName = row["物料名称"].ToString().Trim();
                //修正为不含税
                decimal priceNotTax = 0;
                if (decimal.TryParse(row["单价"].ToString(), out priceNotTax))
                {
                    dtl.PriceNotTax = priceNotTax;
                }
                else
                {
                    dtl.PriceNotTax = 0;
                }

                decimal number = 0;
                if (decimal.TryParse(row["库存总量"].ToString(), out number))
                {
                    dtl.Quantity = number;
                }
                else
                {
                    dtl.Quantity = 0;
                }

                //修正为不含税
                dtl.AmountNotTax = Math.Round(dtl.PriceNotTax*dtl.Quantity, 2);
                dtl.TaxRate = ExcelImportor.RateTax;

                dtl.PriceTax = Math.Round(dtl.PriceNotTax*(1 + ExcelImportor.RateTax), 2);
                dtl.AmountTax = Math.Round(dtl.PriceNotTax*dtl.Quantity*(1 + ExcelImportor.RateTax), 2);
                dtl.Idx = ++idx;
                dtl.BillDtlID = Guid.NewGuid().ToString();
                dtl.OldGoodsCode = dtl.NewGoodsCode;
                dtl.SpecificationType = "";
                dtl.StoreTypeCode = "";
                dtl.StoreTypeName = "";
                dtl.UnitCode = "";
                dtl.UnitName = "";
                dtl.CREATETIME = DateTime.Now.Date;
                dtl.CREATERUSERID = UserSession.CurrentUser.USERID;
                dtl.CREATERNAME = UserSession.CurrentUser.USERNAME;
                dtl.WcfEntityState = EntityState.Added;
                try
                {
                    dtl.ExtVarChar4 = row["备注"].ToString().Trim();
                }
// ReSharper disable EmptyGeneralCatchClause
                catch (Exception)
// ReSharper restore EmptyGeneralCatchClause
                {
                }
                try
                {
                    dtl.StoreLocation = row["库位"].ToString().Trim();
                }
// ReSharper disable EmptyGeneralCatchClause
                catch (Exception)
// ReSharper restore EmptyGeneralCatchClause
                {
                }

                inStoreBillDtls.Add(dtl);

                var goodsCodeType = new GoodsCodeType
                                        {
                                            NewGoodsCode = dtl.NewGoodsCode,
                                            NewGoodsName = dtl.NewGoodsName,
                                            OldGoodsCode = dtl.OldGoodsCode,
                                            SafeStoreQty = 0,
                                            StoreLocation = dtl.StoreLocation,
                                            StoreTypeCode = dtl.StoreTypeCode,
                                            StoreTypeName = dtl.StoreTypeName,
                                            UnitCode = dtl.UnitCode,
                                            UnitName = dtl.UnitName,
                                            ChargeID = UserSession.CurrentUser.USERID,
                                            ChargeName = UserSession.CurrentUser.USERNAME,
                                            GoodsClass = "",
                                            GoodsDesc = ""
                                        };
                client.AddOrValidateGoodsCodeType(goodsCodeType);
            }

            return inStoreBillDtls;
        }

        public List<GoodsCodeType> ImportGoodsCodeTypeFromExcel(string excelFile)
        {
            var list = new List<GoodsCodeType>();
            DataTable excelTable = null;
            var ds = new DataSet();

            var conn =
                new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";" +
                                    "Extended Properties=Excel 8.0;");
            conn.Open();
            //var schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            var tableName = @"物料代码$"; //schemaTable.Rows[0][2].ToString().Trim();//获取 Excel 的表名，默认值是sheet1
            var strSql = "select * from [" + tableName + "]";
            var myData = new OleDbDataAdapter(strSql, conn);
            myData.Fill(ds, tableName);

            conn.Close();
            var client = WmsServiceFactory.GetServiceClient();
            excelTable = ds.Tables[tableName];
            //int idx = 0;
            foreach (DataRow row in excelTable.Rows)
            {
                if (String.IsNullOrEmpty(row["代码"].ToString().Trim()))
                {
                    continue;
                }
                GoodsCodeType goodsCodeType = null;
                var tempList = client.GetGoodsCodeTypesByCondition(String.Format(" and NewGoodsCode = '{0}'",
                                                                                 row["代码"].ToString().Trim()));
                if (tempList.Count > 0)
                {
                    goodsCodeType = tempList[0];
                    goodsCodeType.ExtVarChar2 = row["规格"].ToString().Trim();
                    goodsCodeType.WcfEntityState = EntityState.Modified;
                }
                else
                {
                    goodsCodeType = new GoodsCodeType();
                    goodsCodeType.NewGoodsCode = row["代码"].ToString().Trim();
                    goodsCodeType.NewGoodsName = row["名称"].ToString().Trim();
                    goodsCodeType.OldGoodsCode = row["代码"].ToString().Trim();
                    goodsCodeType.GoodsDesc = "";
                    goodsCodeType.ExtVarChar2 = row["规格"].ToString().Trim();
                    goodsCodeType.ChargeID = UserSession.CurrentUser.USERID;
                    goodsCodeType.ChargeName = UserSession.CurrentUser.USERNAME;
                    goodsCodeType.SafeStoreQty = 0;
                    goodsCodeType.StoreLocation = "";
                    goodsCodeType.WcfEntityState = EntityState.Added;
                }
                client.SaveGoodsCodeTypes(new List<GoodsCodeType> {goodsCodeType});
                list.Add(goodsCodeType);
            }
            return list;
        }

        public List<GoodsCodeType> ImportGoodsCodeTypeFromExcel2(string excelFile)
        {
            var list = new List<GoodsCodeType>();
            DataTable excelTable = null;
            var ds = new DataSet();

            var conn =
                new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFile + ";" +
                                    "Extended Properties=Excel 8.0;");
            conn.Open();
            //var schemaTable = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            const string tableName = @"物料代码$";
            const string strSql = "select * from [" + tableName + "]";
            var myData = new OleDbDataAdapter(strSql, conn);
            myData.Fill(ds, tableName);

            conn.Close();
            var client = WmsServiceFactory.GetServiceClient();
            excelTable = ds.Tables[tableName];
            
            const string newCode = "新的物料代码";
            const string newName = "新的物料名称";
            const string oldCode = "老的物料代码";
            const string unitName = "计量单位名称";
            const string storeLocation = "库位";

            //获得全部的单位
            var listUnit = client.GetUnits();

            foreach (DataRow row in excelTable.Rows)
            {
                if (String.IsNullOrEmpty(row[newCode].ToString().Trim()))
                {
                    continue;
                }
                if (String.IsNullOrEmpty(row[newName].ToString().Trim()))
                {
                    continue;
                }
                GoodsCodeType goodsCodeType = null;
                var tempList = client.GetGoodsCodeTypesByCondition(String.Format(" and ( NewGoodsCode = '{0}' or ( NewGoodsName ='{1}'  and (ExtVarChar3 = '1' or ExtVarChar3 is null or ExtVarChar3 ='' )   ) )  ",
                                                                                 row[newCode].ToString().Trim(), row[newName].ToString().Trim()));
                if (tempList.Count > 0)
                {
                    if (tempList[0].NewGoodsCode == row[newCode].ToString().Trim())
                    {
                        throw new Exception("已经存在新的物料代码：" + tempList[0].NewGoodsCode + "，对应新的物料名称是：" + tempList[0].NewGoodsName);
                    }
                    if (tempList[0].NewGoodsName == row[newName].ToString().Trim())
                    {
                        throw new Exception("已经存在新的物料名称：" + tempList[0].NewGoodsName + "，对应新的物料代码是：" + tempList[0].NewGoodsCode);
                    }
                }
                else
                {
                    var unit = listUnit.Find(u => u.UnitName == row[unitName].ToString().Trim());

                    goodsCodeType = new GoodsCodeType
                                        {
                                            NewGoodsCode = row[newCode].ToString().Trim(),
                                            NewGoodsName = row[newName].ToString().Trim(),
                                            OldGoodsCode = row[oldCode].ToString().Trim(),
                                            GoodsDesc = "",
                                            ExtVarChar2 = "",
                                            ChargeID = UserSession.CurrentUser.USERID,
                                            ChargeName = UserSession.CurrentUser.USERNAME,
                                            SafeStoreQty = 0,
                                            StoreLocation = row[storeLocation].ToString().Trim()
                                        };

                    if(unit != null)
                    {
                        goodsCodeType.UnitCode = unit.UnitCode;
                        goodsCodeType.UnitName = unit.UnitName;
                    }

                    goodsCodeType.WcfEntityState = EntityState.Added;
                }
                //client.SaveGoodsCodeTypes(new List<GoodsCodeType> {goodsCodeType});
                list.Add(goodsCodeType);
            }
            client.SaveGoodsCodeTypes(list);
            return list;
        }

        #endregion
    }
}